from sqlalchemy import create_engine, text, Column, Integer, String
from sqlalchemy.orm import sessionmaker, declarative_base
from WbEntity import WbImageUrl

DATABASE_URL = 'mysql+pymysql://root:root123465@156.238.249.209/py'

# 创建数据库引擎
engine = create_engine(DATABASE_URL, pool_pre_ping=True)
# 创建会话类
Session = sessionmaker(bind=engine)

Base = declarative_base()


def insert_image_url(realurl, pathUrl, uid, uidName):
    """插入数据并自动提交和关闭会话"""
    try:
        with Session() as session:
            # 创建对象
            new_image = WbImageUrl(realurl=realurl, pathUrl=pathUrl, uid=uid, uidName=uidName)
            session.add(new_image)
            session.commit()  # 自动提交
            print("数据插入成功")
    except Exception as e:
        print(f"插入失败: {e}")


def insert_image_url_bulk(image_data_list):
    """批量插入数据并自动提交和关闭会话"""
    try:
        with Session() as session:
            # 使用 bulk_save_objects 批量插入
            session.bulk_save_objects(image_data_list)
            session.commit()  # 提交所有插入的数据
            print(f"{len(image_data_list)} 条数据批量插入成功")
    except Exception as e:
        print(f"批量插入失败: {e}")


def execute_query(query):
    """执行给定的 SQL 查询并返回结果."""
    session = Session()
    try:
        # 执行查询
        result = session.execute(text(query))
        # 返回所有结果
        return result.fetchall()
    except Exception as e:
        print(f"Error: {e}")
        return None
    finally:
        session.close()


def execute_update(query):
    """执行给定的 SQL 查询并返回结果."""
    session = Session()
    try:
        # 执行查询
        result = session.execute(text(query))
        session.commit()
        # 返回所有结果
        print(f"成功更新 {result.rowcount} 条记录的 pathUrl")
        return result.rowcount
    except Exception as e:
        print(f"Error: {e}")
        return None
    finally:
        session.close()
